 /*********************************************************************************
 *      Copyright:  (C) 2025 LingYun IoT System Studio
 *                  All rights reserved.
 *
 *       Filename:  sqlite_server.c
 *    Description:  This file is about server sqlite function 
 *                 
 *        Version:  1.0.0(25/03/21)
 *         Author:  Huang Xiangming <huangxiangming@gmail.com>
 *      ChangeLog:  1, Release initial version on "25/03/21 13:10:30"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <stdlib.h>

#include "sqlite3.h"
#include "sqlite_server.h"
#include "logger.h"
#include "server_function.h"

#define DB_NAME		"ds18b20.db"

static int			table_exist = 0;
static int			tab_exit = 0;
static int			rtb = -1;
static char 		*sqlError = NULL;
static sqlite3 	*db;

//数据库初始化（创建、打开），在while（1）之前
int sqlite_init(void)
{
	int			rv = -1;

	rv = sqlite3_open(DB_NAME, &db);
	if ( rv != SQLITE_OK )
	{
		log_error("open database[%s] error!\n", DB_NAME);
		return -1;
	}

	log_info("open database[%s] successfully!\n", DB_NAME);

	return 0;
}

void sqlite_close(void)
{
	sqlite3_close(db);
	return ;
}

//查询表
int sqlite_table(char *table_name)
{
	char                    query[512];
	int                     rv = -1;
	char                    *sqlError = NULL;

	sprintf(query, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%s';", table_name);
	rv = sqlite3_exec(db, query, table_callback, NULL, &sqlError);
	if ( rv != SQLITE_OK )
	{
		log_warn("Failed to query the table: %s\n", sqlError);
		goto Cleanup;
	}

	if ( table_exist == 0 )     //表存在返回1，不存在返回0
	{
		sprintf(query, "CREATE TABLE [%s] (ID CHAR(10), TIME CHAR(10), TEMPERATURE CHAR(10));", table_name);
		rv = sqlite3_exec(db, query, NULL, NULL, &sqlError);
		if ( rv != SQLITE_OK )
		{
			log_error("Failed to create the table: %s\n", sqlError);
			goto Cleanup;
		}
		log_info("Create table successfully!\n");
	}
	return 0;

Cleanup:
	sqlite3_free(sqlError);
	sqlite_close();
	return -1;
}

static int table_callback(void *exec_para, int n_column, char **column_value, char **column_name)
{
	if ( 1 == n_column )
	{
		table_exist = atoi(*(column_value));
	}

	return 0;
}



int sqlite_inset(data_t *data)
{
	char		sql_inset[512];			//存储SQL语句
	char		*ID = data->id;			//获取id
	char		*TIME = data->sample_time;		//获取时间
	char		*TEMPERATURE = data->temp;		//获取温度
	int			rv = -1;

	if ( !data )
	{
		return -1;
	}

	sprintf(sql_inset, "INSERT INTO %s VALUES ('%s', '%s', '%s');", ID, ID, TIME, TEMPERATURE);
	rv = sqlite3_exec(db, sql_inset, NULL, NULL, &sqlError);
	if ( rv != SQLITE_OK )
	{
		log_warn("Inset data into table failure: %s\n", sqlError);
		sqlite3_free(sqlError);
		return -1;
	}
	else
	{
		log_info("Inset data into table successfully!");
	}

	return 0;
}


